#!/usr/bin/python
# -*- coding: UTF-8 -*-
from Config import *
import pymysql
#where >group by > order by>
db=pymysql.connect(host=MYSQL_URL,db=MYSQL_TABLE,user=MYSQL_USER,passwd=MYSQL_PAW,charset="utf8")
cursor=db.cursor()


def demo():
    global sql_6
    sql_1 = "select deptno,count(*) from emp group by deptno"
    sql_2 = "select deptno,sum(sal) total_sal from emp WHERE job='MANAGER' GROUP BY deptno"
    sql_3 = "select job,count(distinct deptno) from emp where mgr is not null GROUP BY job order by job"
    sql_4 = "select job,count(distinct deptno) from emp where mgr is not null GROUP BY job order by count(DISTINCT deptno) desc,job"
    #
    sql_4_equal_4 = "select job,count(distinct deptno) uni_dept from emp where mgr is not null GROUP BY job order by uni_dept desc,job"
    sql_5 = "select deptno,DATE_FORMAT(hiredate,'%Y'),count(*) from emp GROUP BY deptno,DATE_FORMAT(hiredate,'%Y')"
    #查出每个部门每年雇佣的员工数量，并按部门编号和年份顺序排列
    sql_6 = "select deptno,DATE_FORMAT(hiredate,'%Y') hire_year,count(*) from emp GROUP BY deptno,DATE_FORMAT(hiredate,'%Y') ORDER BY deptno,hire_year"


def subquery():
    #查询所有MANAGER的直接下属的雇员编号和雇员姓名
    sql_1="select empno,ename from emp WHERE mgr in (SELECT empno from emp where job='MANAGER' )"
    #查询没有员工的部门信息
    sql_2="select * from dept WHERE deptno not in (SELECT DISTINCT deptno FROM emp)"
    sql_2_2 = "select * from dept WHERE deptno not in (SELECT  deptno FROM emp)"
    #查询SCOTT的直接下属的雇员编号、雇员姓名和薪水
    sql_3="select empno,ename,sal from emp WHERE mgr in (select empno FROM emp WHERE ename='SCOTT')"
    #查询薪水高于平均薪水40%的雇员信息
    sql_4="select * from emp WHERE sal >1.4*(select avg(sal) FROM emp)"
    cursor.execute(sql_4)
    db.commit()

    resutl = cursor.fetchall()
    print(resutl)

def prior_sql():
    #TODO mysql如何实现层次查询
    sql_1="select level,t.* from emp t start WITH empno=7788 CONNECT by PRIOR empno=mgr"
    cursor.execute(sql_1)
    db.commit()
    result=cursor.fetchall()
    print(result)

def connecnt_sql():
    #等值连接:查询部门ID为20的部门名称、所在城市和雇员编号、雇员姓名
    sql_1="select dname,loc,empno,ename from emp a ,dept b where a.deptno=b.deptno and b.deptno=20"

    #自连接 查询雇员姓名为SCOTT的雇员的雇员编号、薪水和其直属上司的雇员编号、雇员姓名和薪水
    sql_2="select a.empno,a.sal,b.empno,b.ename,b.sal from  emp a,emp b WHERE a.mgr=b.empno and a.ename='SCOTT'"

    #笛卡尔积 做一个3*3的加法表
    #TODO

    #内连接 给部门编号为10的部门的雇员定薪水级别
    sql_3="select empno, ename, sal, grade from emp, salgrade where deptno=10 and sal between losal and hisal"
    sql_4="select empno,ename,sal,grade from emp INNER JOIN salgrade on deptno=10 and sal BETWEEN losal and hisal"

    #反连接 查询部门不在纽约和达拉斯的雇员信息
    sql_5="select * from emp WHERE deptno not in (select deptno FROM dept WHERE loc in ('NEW YORK','DALLAS'))"

    #查询没有员工的部门信息
    sql_6="select * from dept WHERE deptno not in (select deptno from emp)"

    #半连接 查询部门在纽约的雇员信息
    sql_7="select * from emp a where exists (select 1 from dept b where loc='NEW YORK' and a.deptno=b.deptno)"
    sql_8="select a.* from emp a ,dept b where a.deptno=b.deptno and b.loc='NEW YORK'"
    #如果一个雇员的薪水高于2900，那么查出其所在部门的信息
    sql_9="select * from dept a where EXISTS (select 1 from emp b WHERE a.deptno=b.deptno and b.sal>2900)"
    sql_10="select DISTINCT a.* from dept a,emp b WHERE a.deptno=b.deptno and b.sal>2900"

    #外连接 用+
    #查询出所有部门的雇员信息
    sql_11="select  b.deptno,b.dname,a.* from emp a right JOIN dept b on a.deptno=b.deptno"
    #查询出所有部门的雇员数
    sql_12="select b.deptno,count(empno) from emp a right JOIN dept b on a.deptno=b.deptno GROUP by b.deptno"
    #查询出所有部门的职位为经理的雇员数
    sql_13="select b.deptno,count(empno) from emp a RIGHT JOIN dept b on a.deptno=b.deptno and a.job='MANAGER'GROUP BY b.deptno"
    #查询出没有雇员的部门
    sql_14="select b.* ,a.empno from emp a right  JOIN dept b on a.deptno=b.deptno  WHERE a.empno IS NULL"
    cursor.execute(sql_14)
    db.commit()
    result=cursor.fetchall()
    print(result)
connecnt_sql()


